package Mysql_;

import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @Author 汤舒青
 * @Version 1.0
 */
//添加如下的查询方法，包含姓名，qq号，手机号的模糊查找，以及分页
//，同时，要考虑如果某个条件为空（null或者空串），则忽略这个查询条件,
//方法声明如下:
//  search(String sname,String qq,String telephone)
@SuppressWarnings("all")
public class Mysql03 {
    private static final Scanner input = new Scanner(System.in);

    public static void main(String[] args) {
        System.out.println("输入姓名");
        String name = input.nextLine();
        System.out.println("输入qq");
        String qq = input.nextLine();
        System.out.println("输入号码");
        String num = input.nextLine();
        System.out.println("请输入需要查看的页数");
        int pageno = input.nextInt();
        System.out.println("请输入一页要显示多少行");
        int pagesize = input.nextInt();
        search(name, qq, num, pageno, pagesize);

    }

    public static void search(String name, String qq, String telephone, int pageNo, int pageSize) {
        PreparedStatement pr = null;
        Connection con = null;
        ResultSet res=null;
        try {
            con = DBUtil.getConnection();
            List<Object> list = new ArrayList<>();
            String sql = "select * from s_student where 1=1";
            if (name != null && !name.equals("")) {
                sql += "and sname like concat ('%',?,'%')";
                list.add(name);
            }
            if (qq != null && !qq.equals("")) {
                sql += "and qq like concat ('%',?,'%')";
                list.add(qq);
            }
            if (telephone != null && !telephone.equals("")) {
                sql += "and telephone like concat ('%',?,'%')";
                list.add(telephone);
            }
            sql += "limit ?,?";
            list.add((pageNo - 1) * pageSize);
            list.add(pageSize);
            System.out.println(sql + "ddd" + list);
            pr = con.prepareStatement(sql);
            for (int i = 0; i < list.size(); i++) {
                pr.setObject(i + 1, list.get(i));

            }
         res = pr.executeQuery();

            while (res.next()) {
                System.out.println(res.getString(1));
                System.out.println(res.getString(2));
                System.out.println(res.getString(3));
                System.out.println(res.getString(4));


            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(res);
            DBUtil.close(pr);
            DBUtil.close(con);

        }


    }
}